'''
管理非django自行连接生成的数据库实例，sqlserver数据库
提供对这些自定义数据库实例的Tool类进行方便操作
'''
import logging
from django.conf import settings

import inspect
import types

logger = logging.getLogger(__name__)

class CustomDataSourceManager:

    database_config = {} # 自定义数据库配置
    get_funcs = {} # 获取连接函数 key为engine_type

    '''初始化函数'''

    @classmethod
    def init(manager):
        '''初始化模板函数'''
        manager.init_database_config()
        manager.init_get_funcs()

    @classmethod
    def init_get_funcs(manager):
        func_prefix = "get_connection_by_"
        for name, method in inspect.getmembers(manager):
            # 寻找被@classmethod标注的register_by方法
            if method and type(method) == types.MethodType and name.startswith(func_prefix):
                engine_type = name[len(func_prefix):]
                manager.get_funcs[engine_type] = method

    @classmethod
    def init_database_config(manager):
        '''初始化自定义数据库配置，目前只有engine_type和driver自定义的配置'''
        overall_db_config:dict = settings.DATABASES
        for db_name,db_config in overall_db_config.items():
            if db_config.get("CUSTOM_CONFIG"):
                manager.database_config[db_name] = db_config

    @classmethod
    def get_db_config(manager,db_name):
        return manager.database_config.get(db_name,{})

    '''具体获取数据源函数'''

    @classmethod
    def get_connection_by_pyodbc(manager,db_config):
        '''根据pyodbc注册数据源'''
        import pyodbc
        host = db_config["HOST"]
        port = db_config["PORT"]
        server = host+","+port
        database = db_config["NAME"]
        username = db_config["USER"]
        password = db_config["PASSWORD"]
        # 获取自定义配置信息
        custom_config = db_config["CUSTOM_CONFIG"]
        driver = custom_config["driver"]  # 根据你的ODBC驱动版本更改
        # 处理其他连接配置参数
        other_config:dict = custom_config.get("other",{})
        other_text = ""
        for key,value in other_config.items():
            other_text = other_text+key+"="+value+";"
        cnxn_string = (
            f'DRIVER={driver};SERVER={server};DATABASE={database};UID={username};PWD={password};{other_text};'
        )
        # 建立连接
        connection = pyodbc.connect(cnxn_string)
        connection.autocommit = False
        return connection


    @classmethod
    def get_connection_by_pymssql(manager,db_config):
        import pymssql
        '''根据pymssql注册数据源'''
        host = db_config["HOST"]
        port = int(db_config["PORT"])
        database = db_config["NAME"]
        username = db_config["USER"]
        password = db_config["PASSWORD"]
        # 获取自定义配置信息
        custom_config = db_config["CUSTOM_CONFIG"]
        other_config:dict = custom_config.get("other",{})
        overall_config:dict = {**other_config}
        overall_config["host"] = host
        overall_config["port"] = port
        overall_config["database"] = database
        overall_config["user"] = username
        overall_config["password"] = password
        # 建立连接
        # connection = pymssql.connect(host=host,port=port,database=database,user=username,password=password,tds_version="7.0",charset="GBK")
        connection = pymssql.connect(**overall_config)
        connection.autocommit(False) 
        return connection
    
    '''获取数据源连接'''

    @classmethod
    def get_connection(manager,db_name):
        # 校验有效性
        if not db_name:
            return None
        db_config = manager.database_config[db_name]
        # 校验有效性
        if not db_config:
            return None
        # 获取自定义配置
        custom_config = db_config["CUSTOM_CONFIG"]
        engine_type = custom_config["engine_type"]
        get_func = manager.get_funcs[engine_type]
        try:
            connection = get_func(db_config)
        except Exception as e:
            logger.warning(f"连接{db_name}失败,Error:{e}")
            return None
        if connection:
            logger.info(f"成功连接 {db_name}")
            return connection
        logger.info(f"连接正常，但是没有得到连接实例!")
        return None      

    '''结果转换处理函数'''

    @staticmethod
    def to_dict(cursor,rows):
        if not cursor or not rows:
            return []
        '''将query结果转化为dict格式'''
        columns = [col[0] for col in cursor.description]
        dict_rows_list = []
        for row in rows:
            row_dict = {}
            for i in range(0,len(row)):
                key = columns[i]
                value = row[i]
                row_dict[key] = value
            dict_rows_list.append(row_dict)
        return dict_rows_list

    @staticmethod
    def trim(rows):
        if not rows:
            return []
        is_tuple = type(rows[0]) == tuple
        column_length = len(rows[0])
        # 如果是元组数据源
        if is_tuple:
            for row in rows:
                for i in range(0,column_length):
                    if type(row[i]) == str:
                        row[i] = row[i].strip()
        # 如果行记录以字典形式
        else:
            for row_dict in rows:
                for key,value in row_dict.items():
                    if type(value) == str:
                        row_dict[key] = value.strip()
                        if key == "SendSampleMan":
                            CustomDataSourceManager.test_char = value


logger.info("CustomDataSourceManager init...")
CustomDataSourceManager.init()

from abc import abstractmethod,ABC
class AbstractDBTool(ABC):
    '''抽象DB工具类'''
    def __init__(self,anti_sql_injection) -> types.NoneType:
        super().__init__()
        self.anti_sql_injection = anti_sql_injection

    @abstractmethod
    def execute_query(self, query, params=None,return_dict=False):
        pass

    @abstractmethod
    def execute_update(self, query, params=None):
        pass

class CustomDBTool(AbstractDBTool):
    def __init__(self, database,anti_sql_injection="%s"):
        super().__init__(anti_sql_injection=anti_sql_injection)
        """
        初始化DBTool类，并指定数据库。

        :param database: str，连接的数据库名称。如果未提供数据库名称，将抛出异常。
        """
        if not database:
            error_message = "数据库名称是必填项，不能为None或空字符串。"
            logger.error(error_message)
            raise ValueError(error_message)
        self.database = database
        # 重新赋值防sql注入符号
        # 如果还是默认值的话，则需要从配置中重新判断获取
        if anti_sql_injection == "%s":
            db_config = CustomDataSourceManager.get_db_config(self.database)
            if not db_config:
                error_message = "无效的数据库连接名"
                logger.error(error_message)
                raise ValueError(error_message)
            custom_config = db_config["CUSTOM_CONFIG"]
            anti_sql_injection = custom_config.get("anti_sql_injection","%s")
            self.anti_sql_injection = anti_sql_injection

    def execute_query(self, query, params=None,return_dict=False):
        """
        在指定数据库上执行原始查询。

        :param query: str，要执行的SQL查询。
        :param params: tuple或list，包含在查询中的参数。
        :param return_dict True-返回list<dict>
        :return: list，返回查询到的结果行。
        """
        connection = None
        try:
            connection = CustomDataSourceManager.get_connection(self.database)
            if not connection:
                logger.error("ValueError:Empty connection instance")
                return []
            with connection.cursor() as cursor:
                cursor.execute(query, params or [])
                rows = cursor.fetchall()
                # 如果要返回字典
                if return_dict:
                    return CustomDataSourceManager.to_dict(cursor=cursor,rows=rows)
                return rows
        except Exception as e:
            logger.error(f"执行查询时发生错误: {str(e)}")
            return []
        finally:
            if connection:
                connection.close()

    def execute_update(self, query, params=None):
        """
        在指定数据库上执行UPDATE（或INSERT/DELETE）查询。

        :param query: str，要执行的SQL查询。
        :param params: tuple或list，包含在查询中的参数。
        :return: int，受影响的行数，出错时返回-1。
        """
        connection = None
        try:
            connection = CustomDataSourceManager.get_connection(self.database)
            if not connection:
                logger.error("ValueError:Empty connection instance")
                return -1
            with connection.cursor() as cursor:
                cursor.execute(query, params or [])
                return cursor.rowcount  # 返回受影响的行数
        except Exception as e:
            if connection:
                connection.rollback()
            logger.error(f"执行更新时发生错误: {str(e)}")
            return -1
        finally:
            if connection:
                connection.close()






